﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using System.IO;
using AAA.Writer.Format;
using System.Windows.Forms;
using System.Threading;
using AAA.ResultSet;
using System.Drawing;

namespace AAA.Writer
{
    public class ExcelWriter : AbstractWriter
    {
        private const string COLUMN_LETTER = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
        private Excel.ApplicationClass _xlsApp;
        private Excel.Workbook _xlsWorkbook;
        private Excel.Worksheet _xlsSheet;

        private bool OpenWorkbook(string strFile, bool isVisible)
        {
            try
            {
                if (_xlsApp == null)
                    _xlsApp = new Excel.ApplicationClass();
                if(File.Exists(strFile))
                    _xlsWorkbook = _xlsApp.Workbooks.Open(strFile, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true);
                else
                    _xlsWorkbook = _xlsApp.Workbooks.Add(Type.Missing);

                _xlsApp.Visible = isVisible;
                return true;
                //_xlsSheet = (Excel.Worksheet)_xlsApp.Sheets["Summary"];
            }
            catch (Exception ex)
            {
                ErrorMessage = ex.Message + "," + ex.StackTrace;
            }
            return false;
        }

        private bool CloseWorkbook(bool isSaveChange)
        {
            if (_xlsApp == null)
                return true;

            bool isSuccess = true;
            try
            {
                _xlsWorkbook.Close(isSaveChange, Type.Missing, Type.Missing);
                _xlsApp.Quit();                
            }
            catch (Exception ex)
            {
                ErrorMessage = ex.Message + "," + ex.StackTrace;
                isSuccess = false;
            }
            finally
            {
                try
                {
                    Marshal.ReleaseComObject(_xlsSheet);
                    Marshal.ReleaseComObject(_xlsWorkbook);
                    Marshal.ReleaseComObject(_xlsApp);
                }
                catch (Exception ex)
                {
                }
                _xlsApp = null;

            }
            return isSuccess;
        }

        private bool SaveWorkbook(bool isSaveAs)
        {
            try
            {
                if (isSaveAs)
                    _xlsWorkbook.SaveCopyAs(TargetFile);
                else
                    _xlsWorkbook.Save();
            }
            catch (Exception ex)
            {
                ErrorMessage = ex.Message + "," + ex.StackTrace;
            }
            return false;
        }

        private void SelectWorksheet(string strWorksheetName)
        {
            try
            {
                Excel.Worksheet ws;
                _xlsApp.DisplayAlerts = false;
                for (int i = _xlsWorkbook.Worksheets.Count - 1; i >= 0; i--)
                {
                    ws = (Excel.Worksheet)_xlsWorkbook.Worksheets[i + 1];
                    if (ws.Name == strWorksheetName)
                    {
                        _xlsSheet = ws;
                        return;
                    }                    
                }
                _xlsApp.DisplayAlerts = true;
                _xlsSheet = (Excel.Worksheet)_xlsWorkbook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                _xlsSheet.Name = strWorksheetName;                
            }
            catch (Exception ex)
            {
                ErrorMessage = ex.Message + "," + ex.StackTrace;
            }
        }

        private void ClearDefaultSheet()
        {
            Excel.Worksheet ws;
            _xlsApp.DisplayAlerts = false;
            for (int i = _xlsWorkbook.Worksheets.Count - 1; i >= 0; i--)
            {
                ws = (Excel.Worksheet)_xlsWorkbook.Worksheets[i + 1];
                if (ws != null)
                    if ((ws.Name == "Sheet1") ||
                       (ws.Name == "Sheet2") ||
                       (ws.Name == "Sheet3"))
                        ws.Delete();
            }
            _xlsApp.DisplayAlerts = true;            
        }

        private string ColumnLetter(int iColumnIndex)
        {
            string strColumnLetter = "";

            if (iColumnIndex <= 0)
                return strColumnLetter;

            if (iColumnIndex > 16384)
                return strColumnLetter;

            if(iColumnIndex > 702)
                strColumnLetter = COLUMN_LETTER.Substring((iColumnIndex - 1 - COLUMN_LETTER.Length - 676) % 676, 1) +
                                  COLUMN_LETTER.Substring(((iColumnIndex - 1 - COLUMN_LETTER.Length) / COLUMN_LETTER.Length) % COLUMN_LETTER.Length, 1) +
                                  COLUMN_LETTER.Substring((iColumnIndex - 1) % COLUMN_LETTER.Length, 1);
            else if(iColumnIndex > 26)
                strColumnLetter = COLUMN_LETTER.Substring((iColumnIndex - 1) / COLUMN_LETTER.Length - 1, 1) +
                                  COLUMN_LETTER.Substring((iColumnIndex - 1) % COLUMN_LETTER.Length, 1);
            else
                strColumnLetter = COLUMN_LETTER.Substring(iColumnIndex - 1, 1);
                

            return strColumnLetter;
        }

        public override bool Write(Dictionary<string, CellValue> dicValue, object oFormat)
        {
            ExcelFormat excelFormat = (ExcelFormat)oFormat;
            bool isSuccess = false;
            bool isNewFile = false;
            string strFilename;
            int iRowIndex;
            Excel.Range xlsRange = null;
            object[] oValues;

            int iMinRowIndex = int.MaxValue;
            int iMaxRowIndex = -1 * int.MaxValue;

            int iMinColIndex = int.MaxValue;
            int iMaxColIndex = -1 * int.MaxValue;

            try
            {
                if (IsAppend)
                {
                    strFilename = File.Exists(TargetFile) ? TargetFile : SourceFile;
                }
                else
                {
                    strFilename = SourceFile;
                }

                isNewFile = (strFilename == SourceFile);

                OpenWorkbook(strFilename, excelFormat.IsVisible);                

                SelectWorksheet(excelFormat.WorksheetName);
                iRowIndex = excelFormat.StartRow;
                _xlsApp.Calculation = Excel.XlCalculation.xlCalculationManual;

                foreach(CellValue cellValue in dicValue.Values)
                {
                    iMaxColIndex = Math.Max(cellValue.ColIndex, iMaxColIndex);
                    iMinColIndex = Math.Min(cellValue.ColIndex, iMinColIndex);
                    iMaxRowIndex = Math.Max(cellValue.RowIndex, iMaxRowIndex);
                    iMinRowIndex = Math.Min(cellValue.RowIndex, iMinRowIndex);

                    System.Windows.Forms.Application.DoEvents();
                    xlsRange = _xlsSheet.get_Range(ColumnLetter(cellValue.ColIndex) + cellValue.RowIndex,
                                                   Type.Missing);
                    xlsRange.Value = cellValue.Value;
                    if (cellValue.BackColorIndex >= 0)
                        xlsRange.Interior.ColorIndex = cellValue.BackColorIndex;

                    if (cellValue.ForeColorIndex >= 0)
                        xlsRange.Font.ColorIndex = cellValue.ForeColorIndex;

                    iRowIndex++;
                }

                if (excelFormat.AutoFitColumnWidth)
                {
                    xlsRange = _xlsSheet.get_Range(ColumnLetter(iMinColIndex) + iMinRowIndex,
                                                   ColumnLetter(iMaxColIndex) + iMaxRowIndex);
                    xlsRange.Columns.AutoFit();
                }
                _xlsApp.Calculation = excelFormat.Calculation;

                isSuccess = true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                SaveWorkbook(SourceFile != TargetFile);
                CloseWorkbook(isNewFile == false);
            }

            return isSuccess;
        }

        public override bool Write(AAA.ResultSet.IResultSet resultSet, object oFormat)
        {
            IResultSetGroup resultSetGroup;
            ExcelFormat excelFormat = (ExcelFormat)oFormat;

            bool isSuccess = false;
            bool isNewFile = false;
            string strFilename;
            int iRowIndex;
            Excel.Range xlsRange = null;
            object[] oValues;
            string strResultSetName;

            try
            {
                if (resultSet is IResultSetGroup)
                {
                    resultSetGroup = (IResultSetGroup)resultSet;
                }
                else
                {
                    resultSetGroup = new DefaultResultSetGroup();
                    resultSetGroup.AddResultSet(excelFormat.WorksheetName, resultSet, excelFormat);
                }


                if (IsAppend)
                {
                    strFilename = File.Exists(TargetFile) ? TargetFile : SourceFile;
                }
                else
                {
                    strFilename = SourceFile;
                }

                isNewFile = (strFilename == SourceFile); 
                
                OpenWorkbook(strFilename, excelFormat.IsVisible);

                for (int iResultIndex = 0; iResultIndex < resultSetGroup.Count; iResultIndex++)
                {
                    strResultSetName = resultSetGroup.ResultNames()[iResultIndex];

                    resultSetGroup.ActiveResultSet = strResultSetName;
                    excelFormat = (ExcelFormat)resultSetGroup.GetFormat(strResultSetName);

                    SelectWorksheet(strResultSetName);
                    iRowIndex = excelFormat.StartRow;

                    if (isNewFile && excelFormat.HasColumnName)
                    {
                        for (int i = 0; i < excelFormat.ColumnNames.Length; i++)
                        {
                            xlsRange = _xlsSheet.get_Range(ColumnLetter(excelFormat.StartCol + i) + iRowIndex,
                                                           Type.Missing);
                            xlsRange.Value = excelFormat.ColumnNames[i];
                        }
                        iRowIndex++;
                    }

                    if (IsAppend && (excelFormat.IdentifyColumn != null))
                    {
                        while (true)
                        {
                            xlsRange = _xlsSheet.get_Range(excelFormat.IdentifyColumn + iRowIndex, Type.Missing);
                            if (xlsRange.Value == null)
                                break;
                            iRowIndex++;
                        }
                    }

                    _xlsApp.Calculation = Excel.XlCalculation.xlCalculationManual;

                    while (((IResultSet)resultSetGroup).Read())
                    {
                        System.Windows.Forms.Application.DoEvents();
                        oValues = new object[((IResultSet)resultSetGroup).ColumnCount];
                        for (int i = 0; i < ((IResultSet)resultSetGroup).ColumnCount; i++)
                            oValues[i] = ((IResultSet)resultSetGroup).Cells(i);
                        xlsRange = _xlsSheet.get_Range(_xlsSheet.Cells[iRowIndex, excelFormat.StartCol],
                                                       _xlsSheet.Cells[iRowIndex, excelFormat.StartCol + ((IResultSet)resultSetGroup).ColumnCount - 1]);
                        xlsRange.Value2 = oValues;
                        iRowIndex++;
                    }

                    _xlsApp.Calculation = excelFormat.Calculation;
                }
                isSuccess = true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                ClearDefaultSheet();
                SaveWorkbook(SourceFile != TargetFile);
                CloseWorkbook(isNewFile == false);
            }
            return isSuccess;
        }
    }
}
